package com.tisson.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.pojo.AcDoor;
import com.tisson.pojo.Access;
import com.tisson.pojo.Building;

public class AcDoorDAO {
	private String query;

	public List combineQuery(String name, String addr, String building,
			String district, String area) throws SQLException {
		query = "select * from ac_area,ac_building,ac_district,ac_door ";
		String where = "where ac_district.AreaId = ac_area.AreaId "
				+ "and ac_building.DistrictId = ac_district.DistrictId and DoorBuilding = BuildingId";
		String a, b, c, d, e;
		if (addr == null || addr.equals(""))
			a = " and DoorAddress like '%'";
		else
			a = " and DoorAddress = '" + addr + "'";
		if (name == null || name.equals(""))
			b = " and DoorName like '%'";
		else
			b = " and DoorName like '%" + name + "%'";
		if (building == null || building.equals(""))
			c = " and ac_building.BuildingId like '%'";
		else
			c = " and ac_building.BuildingId = '" + building + "'";
		if (district == null || district.equals(""))
			d = " and ac_district.DistrictId like '%'";
		else
			d = " and ac_district.DistrictId = '" + district + "'";
		if (area == null || area.equals(""))
			e = " and ac_district.AreaId like '%'";
		else
			e = " and ac_district.AreaId = '" + area + "'";
		query = query + where + a + b + c + d + e;
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		List accesses = new ArrayList();
		while (rs.next()) {
			Access access = new Access();
			access.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			access.setDoorSerial((String) rs.getObject("DoorSerial"));
			access.setDoorName((String) rs.getObject("DoorName"));
			access.setDoorAddress((String) rs.getObject("DoorAddress"));
			access.setDoorSystem((Integer) rs.getObject("DoorSystem"));
			access.setDoorTic((Integer) rs.getObject("DoorTic"));
			access.setDoorChn((Integer) rs.getObject("DoorChn"));
			access.setDoorDelay((Integer) rs.getObject("DoorDelay"));
			access.setDoorBuilding((String) (rs.getObject("BuildingName")));
			access.setDoorArea((String) (rs.getObject("AreaName")));
			access.setDoorDistrict((String) (rs.getObject("DistrictName")));
			accesses.add(access);
		}

		rs.close();
		stmt.close();
		return accesses;
	}

	public AcDoor findById(String id) throws SQLException {
		query = "select * from ac_door where DoorId = '" + id + "'";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		AcDoor door = new AcDoor();
		while (rs.next()) {
			door.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			door.setDoorSerial((String) rs.getObject("DoorSerial"));
			door.setDoorName((String) rs.getObject("DoorName"));
			door.setDoorAddress((String) rs.getObject("DoorAddress"));
			door.setDoorSystem((Integer) rs.getObject("DoorSystem"));
			door.setDoorTic((Integer) rs.getObject("DoorTic"));
			door.setDoorChn((Integer) rs.getObject("DoorChn"));
			door.setDoorDelay((Integer) rs.getObject("DoorDelay"));
			door.setDoorBuilding(FormatUtil.ConvertUuid(rs
					.getBytes("DoorBuilding")));
		}
		rs.close();
		stmt.close();
		return door;
	}

	public List findByBuildingId(String id) throws SQLException {
		List doorList = new ArrayList();
		query = "select * from ac_door where DoorBuilding = '" + id + "'";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			AcDoor door = new AcDoor();
			door.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			door.setDoorSerial((String) rs.getObject("DoorSerial"));
			door.setDoorName((String) rs.getObject("DoorName"));
			door.setDoorAddress((String) rs.getObject("DoorAddress"));
			door.setDoorSystem((Integer) rs.getObject("DoorSystem"));
			door.setDoorTic((Integer) rs.getObject("DoorTic"));
			door.setDoorChn((Integer) rs.getObject("DoorChn"));
			door.setDoorDelay((Integer) rs.getObject("DoorDelay"));
			door.setDoorBuilding(FormatUtil.ConvertUuid(rs
					.getBytes("DoorBuilding")));
			doorList.add(door);
		}
		rs.close();
		stmt.close();
		return doorList;
	}

	public void save(AcDoor door) throws SQLException {
		query = "insert into ac_door (DoorSerial, DoorName,DoorAddress, DoorSystem,DoorTic,DoorChn,DoorDelay,DoorBuilding)values('"
				+ door.getDoorSerial()
				+ "','"
				+ door.getDoorName()
				+ "','"
				+ door.getDoorAddress()
				+ "',"
				+ door.getDoorSystem()
				+ ","
				+ door.getDoorTic()
				+ ","
				+ door.getDoorChn()
				+ ","
				+ door.getDoorDelay() + ",'" + door.getDoorBuilding() + "')";
		Statement stmt = StatementUtil.getStatement();
		stmt.executeUpdate(query);
		stmt.close();
	}

	public void update(AcDoor door) throws SQLException {
		query = "update ac_door set " + "DoorSerial = '" + door.getDoorSerial()
				+ "',DoorName = '" + door.getDoorName() + "',DoorAddress = '"
				+ door.getDoorAddress() + "',DoorSystem = "
				+ door.getDoorSystem() + ",DoorTic = " + door.getDoorTic()
				+ ",DoorChn = " + door.getDoorChn() + ",DoorDelay = "
				+ door.getDoorDelay() + ",DoorBuilding = '"
				+ door.getDoorBuilding() + "'" + " where DoorId = '"
				+ door.getDoorId() + "'";
		Statement stmt = StatementUtil.getStatement();
		stmt.executeUpdate(query);
		stmt.close();
	}

	public void delete(AcDoor door) throws SQLException {
		query = "delete from ac_door where DoorId = '" + door.getDoorId() + "'";
		Statement stmt = StatementUtil.getStatement();
		stmt.executeUpdate(query);
		stmt.close();
	}
}
